﻿
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using CFM.DAL;
using CFM.DTO;
using CFM.Common;

namespace CFM.API
{       
    public class BillAPI
    {
        public string error = string.Empty;
        private DataHelper _helper = null;
        public BillAPI()
        {
            _helper = new DataHelper();
        }

        public bool Create(Tuple<Bill, List<BillDetail>> bill)
        {
            string billId = MethodHelper.GetBillId();
            string sql = "insert into [Bill] values(@BillId,@TableId,@UserId,@BillDate,@Total,@Status)";
            SqlParameter[] prs = 
            {
                new SqlParameter("@BillId", billId),
                new SqlParameter("@TableId", bill.Item1.TableId),
                new SqlParameter("@UserId", Const.CurrentUser.UserId ),
                new SqlParameter("@BillDate", DateTime.Now),
                new SqlParameter("@Total", bill.Item1.Total),
                new SqlParameter("@Status", true)
            };
            bool result = _helper.ExcuteNonQuery(sql, prs);
            if (result)
            {
                foreach (BillDetail detail in bill.Item2)
                {
                    string sqlDetail = "insert into [BillDetail] values(@BillId,@ProductId,@ProductName,@UnitId,@Price,@Quantity,@DetailTotal); ";
                    sqlDetail += " if exists (select * from FormulaProcess where ProductId = @ProductId)"
                                + " begin"
                                    + " update Product set Quantity = (a.Quantity -tempFs.Quantity*a.Rate)"
                                    + " from Product a join"
                                    + " ("
                                        + " select *  from FormulaProcess where ProductId = @ProductId "
                                    + " ) tempFs"
                                    + " on a.ProductId = tempFs.ProcessProductId"
                                + " end";
                    sqlDetail += " else update Product set Quantity = (Quantity - @Quantity) where ProductId = @ProductId";
                    SqlParameter[] prDetails = 
                    {
                        new SqlParameter("@BillId", billId),
                        new SqlParameter("@ProductId", detail.ProductId),
                        new SqlParameter("@ProductName", detail.ProductName ),
                        new SqlParameter("@UnitId", detail.UnitId),
                        new SqlParameter("@Price", detail.Price),
                        new SqlParameter("@Quantity", detail.Quantity),
                        new SqlParameter("@DetailTotal", detail.DetailTotal)
                    };
                    result = _helper.ExcuteNonQuery(sqlDetail, prDetails);
                }
            }
            error = _helper.error;
            return result;
        }
        public List<Bill> GetAll()
        {
            string sql = "select * from Bill  order by BillDate desc";
            SqlDataReader dr = _helper.ExcuteDataReader(sql, null);
            return _helper.MapDataReaderToObject<Bill>(dr);
        }
        public List<BillDetail> GetBillDetailByBillId(string billId)
        {
            string sql = "select * from BillDetail where BillId = @BillId";
            SqlParameter[] prs = 
            {
                new SqlParameter("@BillId",billId),
            };
            SqlDataReader dr = _helper.ExcuteDataReader(sql, prs);
            return _helper.MapDataReaderToObject<BillDetail>(dr);
        }
        public List<Bill> GetBillByAll(string keyword)
        {
            string sql = "select * from Bill where BillId like N'%" + keyword + "%' or UserId like N'%" + keyword + "%' or BillDate like '%" + keyword + "%'   order by BillDate desc";
            SqlDataReader dr = _helper.ExcuteDataReader(sql, null);
            return _helper.MapDataReaderToObject<Bill>(dr);
        }
        public List<Bill> GetBillByBillId(string billId)
        {
            string sql = "select * from Bill where BillId like N'%" + billId + "%'   order by BillDate desc";
            SqlDataReader dr = _helper.ExcuteDataReader(sql, null);
            return _helper.MapDataReaderToObject<Bill>(dr);
        }
        public List<Bill> GetBillByUerid(string userid)
        {
            string sql = "select * from Bill where UserId like N'%" + userid + "%'   order by BillDate desc";
            SqlDataReader dr = _helper.ExcuteDataReader(sql, null);
            return _helper.MapDataReaderToObject<Bill>(dr);
        }
        public List<Bill> GetBillByBillDate(string billDate)
        {
            string sql = "select * from Bill where BillDate like '%" + billDate + "%'   order by BillDate desc";
            SqlDataReader dr = _helper.ExcuteDataReader(sql, null);
            return _helper.MapDataReaderToObject<Bill>(dr);
        }
    }
}
